package com.db;

import com.to.CategoryTo;
import com.to.ResultTo;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.chart.PieChart;
import javafx.scene.chart.XYChart;

public class DashBoardDao implements Serializable{

        private static final long serialVersionUID = 1L;
        Connection conn = null;
        
        /**
         * This method get all the category from the master_category table
         * and return them as a list.
         * This list is used for generation the Product category combo box
         *
         */
        
        public List<CategoryTo> getAllProductCat(){
                List<CategoryTo> list = new ArrayList<CategoryTo>();
                CategoryTo to = null;
                try{
                        conn = CreateConnection.getCon();
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement("SELECT * FROM  master_category ");
                                
                        ResultSet res = st.executeQuery();
                                  
                        while (res.next()) {
                                to = new CategoryTo();
                                to.setId(res.getInt("cat_id"));
                                to.setDesc(res.getString("cat_desc"));
                                list.add(to);
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(Exception e){
                        e.printStackTrace();
                }
                
                return list;
        }
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products  category   in the form of a Observable list,
         *  which is used to prepare the table for the Category details in dash board1
         */

        public ObservableList<ResultTo> getCatResult(){
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
                
            try{

                double total = 0;
                conn = CreateConnection.getCon();
                String query1 = " select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   ";

                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);

                ResultSet res1 = st1.executeQuery();
                while (res1.next()) {
                        total  = res1.getDouble(1);
                }
                res1.close();
                st1.close();
                DecimalFormat df=new DecimalFormat("0.00");     
                String query = "  select mc.cat_desc,sum(so.net_price),st.target_cost "
                        + " from sales_order so,product_detail pd ,master_category mc,sales_target st " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                "   and mc.cat_id = st.cat_id  group by mc.cat_id";
                PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);

                ResultSet res = st.executeQuery();

                while (res.next()) {
                    String formate = df.format((res.getDouble(2)*100)/total);
                    double percentValue = (Double)df.parse(formate) ;
                    double mrkt = getMrktCost(res.getString(1));
                    data.add(new ResultTo(res.getString(1),res.getDouble(2),percentValue,res.getDouble(3),mrkt,mrkt,res.getDouble(2)));
                }
                res.close();
                st.close();
                conn.close();
            }catch(SQLException e){
                    e.printStackTrace();
            }catch(ParseException e){

            }

            return data;
        }
        
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products for a given product category   in the form of a Observable list,
         *  which is used to prepare the product pie chart
         */
    
        @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getDataCatwise(String cat) {
                
                ObservableList data = FXCollections.observableArrayList();
                try {
                        double total = 0;
                        conn = CreateConnection.getCon();
                        String query1 = "  select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  ";
                                        
                        PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                        st1.setString(1, cat);
                        ResultSet res1 = st1.executeQuery();
                        while (res1.next()) {
                                total  = res1.getDouble(1);
                        }
                        res1.close();
                        st1.close();
                        //conn = CreateConnection.getCon();
                        String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
                                        "  group by so.product_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        st.setString(1, cat);
                        ResultSet res = st.executeQuery();
                           System.out.println("total :" + total);
                           DecimalFormat df=new DecimalFormat("0.00");

                        while (res.next()) {
                            double temp = Math.round(res.getDouble(2)*100)/total;
                            
                            String formate = df.format(temp);
                           // Number  finalValue =df.parse(formate) ;
                            // System.out.println("total by :" + res.getDouble(2) +" and by 100 " +(res.getDouble(2)*100)/total + "final val : "+ finalValue);
                                data.add(new PieChart.Data(res.getString(1),new Double(formate)));

                        }
                        res.close();
                        st.close();
                        conn.close();
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return data;
                
                
        }
        
        
        /**
         * This method get the total sales amount (net price) and the percentage of total sales
         *  for every products for a given product category   in the form of a Observable list,
         *  which is used to prepare the product table
         */
        public ObservableList<ResultTo> getProdResult(String cat){
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
                
            try{
                conn = CreateConnection.getCon();
                double total = 0;
                conn = CreateConnection.getCon();
                String query1 = "  select sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, cat);
                ResultSet res1 = st1.executeQuery();
                while (res1.next()) {
                        total  = res1.getDouble(1);
                }
                res1.close();
                st1.close();
                DecimalFormat df=new DecimalFormat("0.00");
                        
                String query = "  select pd.product_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  " +
                                "  group by so.product_id";
                PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                st.setString(1, cat);
                ResultSet res = st.executeQuery();
                                  
                while (res.next()) {
                    double temp = Math.round(res.getDouble(2)*100)/total;
                    String formate = df.format(temp);
                    
                    data.add(new ResultTo(res.getString(1),res.getDouble(2),new Double(formate)));
                }
                res.close();
                st.close();
                conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                        
            return data;
        }
        
        
        /**
         * This method get the total sales amount (net price)
         *  for every  product category   in the form of a Chart series,
         *  which is used to prepare the product category bar chart
         */
        @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                        "  group by mc.cat_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getString(1),res.getDouble(2)));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
        
        /**
         * This method get the total sales amount of all products
         */
        
        public double getTotalSales(){
                double total = 0;
                try{
                        conn = CreateConnection.getCon();
                        String query = "select sum(so.net_price) from sales_order so ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                        while (res.next()) {
                                total = res.getDouble(1);
                        }
                        res.close();
                        st.close();
                        conn.close();
                
                }catch(SQLException e){
                        e.printStackTrace();
                }
        return total;
        }

        
        /**
         * Getting the total marketing expenditure of the products
         * in a particular month
         * and for a particular category
         * in form of a pie chart
         */
        
        @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getMarketingDataCatwise(String catName,int month,int year) {
            
            ObservableList data = FXCollections.observableArrayList();
            try {
                double total = 0;
                conn = CreateConnection.getCon();
                //getting the total marketing cost for of a category
                String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  MONTH(pd.start_date) = ?  and  YEAR(pd.start_date) = ? "
                        + " group by  cat.cat_id ; ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, catName);
                st1.setInt(2, month);
                st1.setInt(3, year);
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        total  = res.getDouble(1);
                }
                res.close();
                st1.close();
                
                //getting marketing cost for all products for a category
                
                DecimalFormat df=new DecimalFormat("0.00");
                String query2 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  MONTH(pd.start_date) = ?  and  YEAR(pd.start_date) = ? "
                        + " group by pd.product_id ;";
                st1 = (PreparedStatement) conn.prepareStatement(query2);
                st1.setString(1, catName);
                st1.setInt(2, month);
                st1.setInt(3, year);
                res = st1.executeQuery();

                while (res.next()) {
                    double temp = Math.round(res.getDouble(1)*100)/total;
                    String formate = df.format(temp);
                    data.add(new PieChart.Data(res.getString(2),new Double(formate)));
                }
                res.close();
                st1.close();
                conn.close();
                        
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
            }
                return data;
                
                
        }
        
        
         /**
         * Getting the total marketing expenditure of the products
         * in a particular month
         * and for a particular category
         * in form of a Table view
         */
        
        public ObservableList<ResultTo> getMarketingCatWise(String catName, int month, int year){
            ObservableList<ResultTo> data = FXCollections.observableArrayList();
            try{
                conn = CreateConnection.getCon();
                double total = 0;
                
                //getting the total marketing cost for of a category
                String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  MONTH(pd.start_date) = ?  and  YEAR(pd.start_date) = ? "
                        + " group by  cat.cat_id ; ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, catName);
                st1.setInt(2, month);
                st1.setInt(3, year);
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        total  = res.getDouble(1);
                }
                res.close();
                st1.close();
                
                //getting marketing cost for all products for a category
                
                DecimalFormat df=new DecimalFormat("0.00");
                String query2 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , prd.product_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  and  MONTH(pd.start_date) = ?  and  YEAR(pd.start_date) = ? "
                        + " group by pd.product_id ;";
                st1 = (PreparedStatement) conn.prepareStatement(query2);
                st1.setString(1, catName);
                st1.setInt(2, month);
                st1.setInt(3, year);
                res = st1.executeQuery();
                while (res.next()) {
                    double temp = Math.round(res.getDouble(1)*100)/total;
                    String formate = df.format(temp);
                    
                    data.add(new ResultTo(res.getString(2),res.getDouble(1),new Double(formate)));
                }
                res.close();
                st1.close();
                conn.close();
                
            }catch(SQLException e){
                    e.printStackTrace();
            }

            return data;
        }
        
        /**
         * Get all the distinct month and year from the promotion_details to generate
         * Period drop down in the dashboard2
         */
        
        public List<String> getPeriod(){
            List<String> list = new ArrayList<String>();
            String period = null;
            try{
                conn = CreateConnection.getCon();
                PreparedStatement ps = conn.prepareStatement("select distinct MONTH(pd.start_date), YEAR(pd.start_date) from promotion_details pd");
                ResultSet res = ps.executeQuery();
                while(res.next()){
                    period = new String();
                    period = getMonthName(res.getInt(1)) + " - " + res.getInt(2);
                    list.add(period);
                }
            }catch(SQLException e){
                    e.printStackTrace();
            }
            
            return list;
        }
        
        
        /**
         * This method returns the month name from the month number
         */
        private String getMonthName(int month){
            String monthString = null;
            switch (month) {
                case 1:  monthString = "JAN";
                         break;
                case 2:  monthString = "FEB";
                         break;
                case 3:  monthString = "MAR";
                         break;
                case 4:  monthString = "APR";
                         break;
                case 5:  monthString = "MAY";
                         break;
                case 6:  monthString = "JUN";
                         break;
                case 7:  monthString = "JUL";
                         break;
                case 8:  monthString = "AUG";
                         break;
                case 9:  monthString = "SEP";
                         break;
                case 10: monthString = "OCT";
                         break;
                case 11: monthString = "NOV";
                         break;
                case 12: monthString = "DEC";
                         break;
               
            }
            
            return monthString;
        }

/**
 * This method to return the marketing expenditure of a category in a series format.
 * Its used to generate the area chart for marketing cost in the marketing dash board.
 */
    @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<Integer,Double> getAreaMarketChart(final String catName, final int month,final int year){
                XYChart.Series< Integer,Double> aSeries = new XYChart.Series<Integer,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select prd.week_no, sum(prd.planed_volume * prd.discount) "
                                + " from promotion_details prd,  product_detail pd, master_category mc "
                                + " where MONTH(prd.start_date) = ? and YEAR(prd.start_date) = ? "
                                + " and prd.product_id = pd.product_id and pd.product_cat_id = mc.cat_id and mc.cat_desc = ? "
                                + " group by prd.week_no";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        st.setInt(1, month);
                        st.setInt(2, year);
                        st.setString(3, catName);
                        ResultSet res = st.executeQuery();
                          aSeries.getData().add(new XYChart.Data(0,0));
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getInt(1),res.getDouble(2)));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
    /**
     * This method to return the total sale of a category in a series format.
     * Its used to generate the area chart for total sale in the marketing dash board.
     */
        @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<Integer,Double> getAreaSaleChart(final String catName, final int month,final int year){
                XYChart.Series< Integer,Double> aSeries = new XYChart.Series<Integer,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select so.week_no, sum(so.net_price) from sales_order so , product_detail pd, master_category mc "
                                + " where MONTH(so.sales_date) = ? and YEAR(so.sales_date) = ?  and so.product_id = pd.product_id "
                                + " and pd.product_cat_id = mc.cat_id and mc.cat_desc = ?  group by so.week_no";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        st.setInt(1, month);
                        st.setInt(2, year);
                        st.setString(3, catName);
                        ResultSet res = st.executeQuery();
                          aSeries.getData().add(new XYChart.Data(0,0));
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getInt(1),res.getDouble(2)));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }

 @SuppressWarnings({ "rawtypes", "unchecked" })
  public ObservableList getAllCatPie() {
                
                ObservableList data = FXCollections.observableArrayList();
                try {
                        
                        conn = CreateConnection.getCon();
                        
                        String query = "  select mc.cat_desc,sum(so.net_price)from sales_order so,product_detail pd ,master_category mc " +
                                        " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                                        "  group by mc.cat_id";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                           DecimalFormat df=new DecimalFormat("0.00");

                        while (res.next()) {
                            double temp = Math.round(res.getDouble(2));
                            
                            String formate = df.format(temp);
                             data.add(new PieChart.Data(res.getString(1),new Double(formate)));

                        }
                        res.close();
                        st.close();
                        conn.close();
                        
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
                
                return data;
                
                
        }
 
 public double getCatSalesValue(String catName){
	 double value =0.0;
	 try{
		 conn = CreateConnection.getCon();
		 String query = "  select sum(so.net_price) from sales_order so,product_detail pd ,master_category mc " +
                 " where so.product_id = pd.product_id and  pd.product_cat_id = mc.cat_id   " +
                 " and mc.cat_desc = ? ";
		 PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
		 st.setString(1, catName);
		 ResultSet res = st.executeQuery();
		 DecimalFormat df=new DecimalFormat("0.00");
		 while (res.next()) {
             double temp = Math.round(res.getDouble(1)/1000);
             String formate = df.format(temp);
             value = new Double(formate);

         }
         res.close();
         st.close();
         conn.close();
	 }catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
	 }
	 return value;
 }
 
 public double getCatMrktValue(String catName){
	 double value =0.0;
	 try{
		 conn = CreateConnection.getCon();
		 String query = "select sum(pd.discount * pd.planed_volume) as Marketing_cost  "
                 + " from promotion_details pd ,product_detail prd, master_category cat "
                 + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                 + " and cat.cat_desc = ?  ";
		 PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
		 st.setString(1, catName);
		 ResultSet res = st.executeQuery();
		 DecimalFormat df=new DecimalFormat("0.00");
		 while (res.next()) {
             double temp = Math.round(res.getDouble(1)/1000);
             String formate = df.format(temp);
             value = new Double(formate);

         }
         res.close();
         st.close();
         conn.close();
	 }catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
	 }
	 return value;
 }
 
  @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getTargetBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "  select mc.cat_desc,st.target_cost from sales_target st,master_category mc where st.cat_id = mc.cat_id ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getString(1),res.getDouble(2)));
                                System.out.println(res.getString(1)+"----"+res.getDouble(2));
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
  @SuppressWarnings({ "unchecked", "rawtypes" })
  public XYChart.Series<String,Double> getMrktBarChart(){
                XYChart.Series< String,Double> aSeries = new XYChart.Series<String,Double>();
                try{
                        conn = CreateConnection.getCon();
                        String query = "select sum(pd.discount * pd.planed_volume) as Marketing_cost , cat.cat_desc "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        
                        + " group by  cat.cat_id ; ";
                        PreparedStatement st = (PreparedStatement) conn.prepareStatement(query);
                        
                        ResultSet res = st.executeQuery();
                          
                        while (res.next()) {
                                aSeries.getData().add(new XYChart.Data(res.getString(2),res.getDouble(1)));
                               
                        }
                        res.close();
                        st.close();
                        conn.close();
                }catch(SQLException e){
                        e.printStackTrace();
                }
                return aSeries;
                
        }
  
  public double getMrktCost(String cat){
      double mrktVal = 0.0;
      try{
          conn = CreateConnection.getCon();
           String query1 = "select sum(pd.discount * pd.planed_volume) as Marketing_cost "
                        + " from promotion_details pd ,product_detail prd, master_category cat "
                        + " where pd.product_id = prd.product_id  and prd.product_cat_id = cat.cat_id "
                        + " and cat.cat_desc = ?  "
                        + " group by  cat.cat_id  ";
                                        
                PreparedStatement st1 = (PreparedStatement) conn.prepareStatement(query1);
                st1.setString(1, cat);
                
                ResultSet res = st1.executeQuery();
                while (res.next()) {
                        mrktVal  = res.getDouble(1);
                }
                res.close();
                st1.close();
          
      }catch(Exception e){
          e.printStackTrace();
      }
      return mrktVal;
  }
}